Imports eyeLog.Tools

Namespace Core
  Public Class Database
#Region "Private Members"
    Private _parent As Kernel

    Private mSystemColumns As New List(Of Column)
    Private mSchema As String
    Private mTable As String
    Private mTrigger As String
    Private mOldStatus As DatabaseStatuses
    Private mNewStatus As DatabaseStatuses
    Private mEvents As DDL_EVENTS
#End Region

#Region "Constructors"
    Sub New(ByVal parent As Kernel, ByVal row As DataRow)
      Dim query As String
      Dim cn As SqlConnection
      Dim cmd As SqlCommand
      Dim item As Column
      Dim dr As SqlDataReader

      ' ======================================================================
      ' === PRE-PROCESS                                                    ===
      ' ======================================================================
      ' === Save the parameters ==============================================
      _parent = parent
      If row Is Nothing Then
        mOldStatus = DatabaseStatuses.None
        mSchema = IIf(_parent.UseSchema = True, PFX_SCHEMA, "dbo")
        mTable = IIf(_parent.UseSchema = True, String.Format("{0}{1}", _parent.InitialCatalog, SFX_TRIGGER_DDL), String.Format("{0}_{1}{2}", PFX_SCHEMA, _parent.InitialCatalog, SFX_TRIGGER_DDL))
      Else
        mOldStatus = DatabaseStatuses.Installed
        mSchema = row("tblSchema")
        mTable = row("tblName")
      End If
      mNewStatus = mOldStatus

      cn = Connection(_parent.ConnectionString)
      cmd = cn.CreateCommand

      ' ======================================================================
      ' === PROCESS                                                        ===
      ' ======================================================================
      ' === Pre-fill the system columns ======================================
      mSystemColumns.Add(New Column(Me, COL_EVENT_TYPE, "varchar", 128, 128, 0, Nothing, False))
      mSystemColumns.Add(New Column(Me, COL_POST_TIME, "datetime", 0, 0, 0, Nothing, False))
      mSystemColumns.Add(New Column(Me, COL_SPID, "int", 0, 0, 0, Nothing, False))
      mSystemColumns.Add(New Column(Me, COL_SERVER_NAME, "varchar", 128, 128, 0, Nothing, False))
      mSystemColumns.Add(New Column(Me, COL_LOGIN_NAME, "varchar", 128, 128, 0, Nothing, False))
      mSystemColumns.Add(New Column(Me, COL_USER_NAME, "varchar", 128, 128, 0, Nothing, False))
      mSystemColumns.Add(New Column(Me, COL_DATABASE_NAME, "varchar", 128, 128, 0, Nothing, False))
      mSystemColumns.Add(New Column(Me, COL_SCHEMA_NAME, "varchar", 128, 128, 0, Nothing, False))
      mSystemColumns.Add(New Column(Me, COL_OBJECT_NAME, "varchar", 128, 128, 0, Nothing, False))
      mSystemColumns.Add(New Column(Me, COL_OBJECT_TYPE, "varchar", 128, 128, 0, Nothing, False))
      mSystemColumns.Add(New Column(Me, COL_COMMAND_TEXT, "varchar", -1, 0, 0, Nothing, False))
      mSystemColumns.Add(New Column(Me, COL_RAW_DATA, "xml", 0, 0, 0, Nothing, False))

      ' === Retrieve the columns =============================================
      query = String.Format("SELECT C.name AS colName, T.name AS colType, C.length AS colLength, ISNULL(C.prec,0) AS colPrecision, C.xscale AS colScale FROM sysColumns C, sysTypes T WHERE C.xtype=T.xtype AND T.name NOT IN ('sysname', 'id', 'tid', 'empid') AND id = OBJECT_ID('[{0}].[{1}].[{2}]') ORDER BY colName", _parent.InitialCatalog, mSchema, mTable)
      cmd.CommandText = query
      dr = cmd.ExecuteReader
      While dr.Read
        For Each item In mSystemColumns
          If item.Name = dr("colName") Then
            item.OldStatus = ColumnStatuses.Installed ' If found, then it was installed
            item.SetInstallAction(ColumnActions.None)
          End If
        Next
      End While
      dr.Close()

      ' ======================================================================
      ' === POST-PROCESS                                                   ===
      ' ======================================================================
      cn.Close()
    End Sub
#End Region

#Region "Properties"
    ReadOnly Property Parent() As Kernel
      Get
        Return _parent
      End Get
    End Property

    ReadOnly Property Schema() As String
      Get
        Return mSchema
      End Get
    End Property

    ReadOnly Property Table() As String
      Get
        Return mTable
      End Get
    End Property

    ' TODO: Change that property!!!
    ReadOnly Property Name() As String
      Get
        Return mTable
      End Get
    End Property

    'ReadOnly Property SystemColumns() As SortedList(Of String, Column)
    '  Get
    '    Return mSystemColumns
    '  End Get
    'End Property

    ReadOnly Property SystemColumns() As List(Of Column)
      Get
        Return mSystemColumns
      End Get
    End Property

    ReadOnly Property OldStatus() As DatabaseStatuses
      Get
        Return mOldStatus
      End Get
    End Property

    Property Events() As DDL_EVENTS
      Get
        Return mEvents
      End Get
      Set(ByVal value As DDL_EVENTS)
        mEvents = value
      End Set
    End Property

    Property NewStatus() As DatabaseStatuses
      Get
        Return mNewStatus
      End Get
      Set(ByVal value As DatabaseStatuses)
        Dim item As Column
        Dim sysAction As ColumnActions

        mNewStatus = value

        If mOldStatus <> mNewStatus Then
          Select Case mNewStatus
            Case DatabaseStatuses.None
              sysAction = ColumnActions.Remove
            Case DatabaseStatuses.Installed
              sysAction = ColumnActions.Install
#If DEBUG Then
            Case Else
              Stop
              sysAction = ColumnActions.None
              mNewStatus = mOldStatus
#End If
          End Select

          For Each item In mSystemColumns
            item.SetInstallAction(sysAction)
          Next
        End If
      End Set
    End Property
#End Region

#Region "Methods"
    Public Sub GetScript(ByVal script As List(Of String))
      Dim info As String

      ' ======================================================================
      ' === PRE-PROCESS                                                    ===
      ' ======================================================================
      info = String.Format("--- Table: [{0}].[{1}{2}]", PFX_SCHEMA, _parent.InitialCatalog, SFX_TRIGGER_DDL)
      script.Add(String.Format("{0}{1}", info, New String("-", 78 - info.Length)))
      mSchema = IIf(_parent.UseSchema = True, PFX_SCHEMA, "dbo")
      mTable = IIf(_parent.UseSchema = True, String.Format("{0}{1}", _parent.InitialCatalog, SFX_TRIGGER_DDL), String.Format("{0}_{1}{2}", PFX_SCHEMA, _parent.InitialCatalog, SFX_TRIGGER_DDL))
      mTrigger = String.Format("{0}{1}{2}", PFX_TRIGGER_DDL, _parent.InitialCatalog, SFX_TRIGGER_DDL)

      ' ======================================================================
      ' === PROCESS                                                        ===
      ' ======================================================================
      ' === Drop the previous trigger definition =============================
      AddScriptItem(script, GetScriptDropObject(mSchema, mTrigger, ObjectTypes.DdlTrigger))
      script.Add("GO")
      script.Add(Nothing)

      ' === Script the table =================================================
      If mOldStatus <> mNewStatus Then
        GetScriptTable(script)
      End If
      ' === Script the trigger ===============================================
      GetScriptTrigger(script)

      ' ======================================================================
      ' === POST-PROCESS                                                   ===
      ' ======================================================================
    End Sub

    Private Sub GetScriptTable(ByVal script As List(Of String))
      Dim rc As New System.Text.StringBuilder
      Dim item As Column
      Dim colAs As New Column(Me, COL_UPDATED_AS, "char", 1, 0, 0, "'I'", True)

      If ((mNewStatus And DatabaseStatuses.Installed) <> DatabaseStatuses.Installed) Then
        ' --- Drop the history table -----------------------------------------
        rc.AppendLine(GetScriptDropObject(mSchema, mTable, ObjectTypes.Table))
        rc.AppendLine(GetScriptDropObject("dbo", String.Format("{0}_{1}", PFX_SCHEMA, mTable), ObjectTypes.Table))
      Else
        ' --- Create the history table -------------------------------------
        ' Recalculate the schema: the policy might have changed by the user
        rc.AppendFormat("{0}CREATE TABLE [{1}].[{2}] (", vbCrLf, mSchema, mTable)
        rc.AppendFormat("{0}   [{1}] int IDENTITY(1,1) NOT NULL PRIMARY KEY,", vbCrLf, COL_ID)
        For Each item In mSystemColumns
          item.SetInstallAction(ColumnActions.Install)
          rc.AppendFormat("{0}   {1},", vbCrLf, item.ToString())
        Next
        rc.Remove(rc.Length - 1, 1)
        rc.AppendLine(");")
      End If

      AddScriptItem(script, rc.ToString())
      script.Add("GO")
      script.Add(Nothing)
    End Sub

    Private Sub GetScriptTrigger(ByVal script As List(Of String))
      Dim temp As New System.Text.StringBuilder
      Dim events As List(Of String)
      Dim parser As EnumTree

      If ((mNewStatus And DatabaseStatuses.Installed) = DatabaseStatuses.Installed) Then
        parser = New EnumTree(GetType(DDL_EVENTS))
        events = parser.ParseValue(mEvents)

        ' === Create the trigger =============================================
        temp = New System.Text.StringBuilder
        temp.AppendFormat("{0}CREATE TRIGGER [{1}] ON DATABASE FOR", vbCrLf, mTrigger)
        For Each v As String In events
          temp.AppendFormat("{0}   {1},", vbCrLf, v)
        Next
        temp.Remove(temp.Length - 1, 1)

        temp.AppendFormat("{0}AS", vbCrLf)
        temp.AppendFormat("{0}DECLARE @RawData XML;", vbCrLf)
        temp.AppendFormat("{0}", vbCrLf)
        temp.AppendFormat("{0}SET @RawData = EVENTDATA()", vbCrLf)
        temp.AppendFormat("{0}", vbCrLf)
        temp.AppendFormat("{0}INSERT INTO [{1}].[{2}]", vbCrLf, mSchema, mTable)
        temp.AppendFormat("{0}           ([{1}]", vbCrLf, COL_EVENT_TYPE)
        temp.AppendFormat("{0}           ,[{1}]", vbCrLf, COL_POST_TIME)
        temp.AppendFormat("{0}           ,[{1}]", vbCrLf, COL_SPID)
        temp.AppendFormat("{0}           ,[{1}]", vbCrLf, COL_SERVER_NAME)
        temp.AppendFormat("{0}           ,[{1}]", vbCrLf, COL_LOGIN_NAME)
        temp.AppendFormat("{0}           ,[{1}]", vbCrLf, COL_USER_NAME)
        temp.AppendFormat("{0}           ,[{1}]", vbCrLf, COL_DATABASE_NAME)
        temp.AppendFormat("{0}           ,[{1}]", vbCrLf, COL_SCHEMA_NAME)
        temp.AppendFormat("{0}           ,[{1}]", vbCrLf, COL_OBJECT_NAME)
        temp.AppendFormat("{0}           ,[{1}]", vbCrLf, COL_OBJECT_TYPE)
        temp.AppendFormat("{0}           ,[{1}]", vbCrLf, COL_COMMAND_TEXT)
        temp.AppendFormat("{0}           ,[{1}])", vbCrLf, COL_RAW_DATA)
        temp.AppendFormat("{0}       VALUES", vbCrLf)
        temp.AppendFormat("{0}           (@RawData.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)')", vbCrLf)
        temp.AppendFormat("{0}           ,@RawData.value('(/EVENT_INSTANCE/PostTime)[1]', 'nvarchar(100)')", vbCrLf)
        temp.AppendFormat("{0}           ,@RawData.value('(/EVENT_INSTANCE/SPID)[1]', 'nvarchar(100)')", vbCrLf)
        temp.AppendFormat("{0}           ,@RawData.value('(/EVENT_INSTANCE/ServerName)[1]', 'nvarchar(100)')", vbCrLf)
        temp.AppendFormat("{0}           ,@RawData.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(100)')", vbCrLf)
        temp.AppendFormat("{0}           ,@RawData.value('(/EVENT_INSTANCE/UserName)[1]', 'nvarchar(100)')", vbCrLf)
        temp.AppendFormat("{0}           ,@RawData.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'nvarchar(100)')", vbCrLf)
        temp.AppendFormat("{0}           ,@RawData.value('(/EVENT_INSTANCE/SchemaName)[1]', 'nvarchar(100)')", vbCrLf)
        temp.AppendFormat("{0}           ,@RawData.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(100)')", vbCrLf)
        temp.AppendFormat("{0}           ,@RawData.value('(/EVENT_INSTANCE/ObjectType)[1]', 'nvarchar(100)')", vbCrLf)
        temp.AppendFormat("{0}           ,@RawData.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'nvarchar(100)')", vbCrLf)
        temp.AppendFormat("{0}           ,@RawData)", vbCrLf)
        AddScriptItem(script, temp.ToString)
        script.Add("GO")
        script.Add(Nothing)
        script.Add(String.Format("ENABLE TRIGGER [{0}] ON DATABASE", mTrigger))
        script.Add("GO")
        script.Add(Nothing)
      End If
    End Sub
#End Region
  End Class
End Namespace